package com.ezjie.ielts.db;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.ezjie.ielts.core.helper.AppDataBaseHelper;
import com.ezjie.ielts.model.AudioFile;
import com.ezjie.ielts.model.GroupInfo;

public class GroupInfoDB {
	private static AppDataBaseHelper dbHelper;
	private SQLiteDatabase db;
	private QuestionInfoDB questionDB;
	private AudioFileDB audioFileDb;

	public GroupInfoDB(Context context) {
		dbHelper = AppDataBaseHelper.getInstance(context);
		questionDB = new QuestionInfoDB(context);
		audioFileDb = new AudioFileDB(context);
	}

	
	/**
	 * 查找问题组 
	 * @param position 从什么位置开始
	 * @param type 获取的类型
	 * @param category_id 获取真题的id
	 * @param getType 阅读=0or听力=1
	 */
	public synchronized ArrayList<GroupInfo> query(int position, String type, String category_id,int getType) {
		db = dbHelper.getReadableDatabase();

		ArrayList<GroupInfo> lists = new ArrayList<GroupInfo>();

		String sql = "select * from " + AppDataBaseHelper.GROUPINFO + " where "
				+ (type == null ? "" : "group_cate='" + type + "'")
				+ (category_id == null ? "" : "category_id=" + category_id) +(getType==0?" and audio='' ":"") + " order by _id asc";
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.getCount() <= position) {
			position = 0;
		}
		while (cursor.moveToNext()) {
			if (cursor.getPosition() >= position) {
				GroupInfo info = new GroupInfo();
				info.g_id = cursor.getString(1);
				info.category_id = cursor.getString(2);
				info.group_name = cursor.getString(3);
				info.group_cate = cursor.getString(4);
				info.group_type = cursor.getString(5);
				info.content = cursor.getString(6);
				info.img_1 = cursor.getString(7);
				info.img_2 = cursor.getString(8);
				info.audio = cursor.getString(9);
				info.audiofile = cursor.getString(10);
				info.is_right = cursor.getString(11);
				info.updateTime = cursor.getString(12);
				info.book_name = cursor.getString(13);
				info.task = cursor.getString(14);
				info.questions_list = questionDB.groupQuery(db, info.g_id);
				lists.add(info);
			}

		}
		cursor.close();
		db.close();

		return lists;
	}

	// 查找问题组
	public synchronized Set<String> queryListener(SQLiteDatabase db) {

		StringBuilder sb = new StringBuilder();
		Set<AudioFile> set1 = new HashSet<AudioFile>();
		set1 = audioFileDb.queryList(db);
		int i = 0;
		for (AudioFile file : set1) {
			sb.append("'");
			sb.append(file.category_id);
			sb.append("'");
			if (i < set1.size() - 1) {
				sb.append(",");
				i++;
			}
		}

		Set<String> sets = new HashSet<String>();
		String sql = "select * from " + AppDataBaseHelper.GROUPINFO + " where " + "category_id in ( " + sb + " )";
		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			GroupInfo info = new GroupInfo();
			info.g_id = cursor.getString(1);
			info.category_id = cursor.getString(2);
			info.group_name = cursor.getString(3);
			info.group_cate = cursor.getString(4);
			info.group_type = cursor.getString(5);
			info.content = cursor.getString(6);
			info.img_1 = cursor.getString(7);
			info.img_2 = cursor.getString(8);
			info.audio = cursor.getString(9);
			info.audiofile = cursor.getString(10);
			info.is_right = cursor.getString(11);
			info.updateTime = cursor.getString(12);
			info.book_name = cursor.getString(13);
			info.task = cursor.getString(14);
			// if (null != info.audiofile) {
			sets.add(info.category_id);
			// }
		}
		cursor.close();

		return sets;
	}

	// 删除问题组
	public synchronized void delete() {
		db = dbHelper.getWritableDatabase();

		String sql = "delete from " + AppDataBaseHelper.GROUPINFO;
		db.execSQL(sql);
		db.close();
	}

	// 更新问题组
	public synchronized void update() {
		db = dbHelper.getWritableDatabase();

		String sql = "update " + AppDataBaseHelper.GROUPINFO + " set updateTime= ? ";
		db.execSQL(sql, new Object[] { 1 });
		db.close();
	}

	/**
	 * 更新音频本地文件路径
	 * 
	 * @param g_id
	 * @param audioFile
	 */
	public synchronized void updateAudioFile(String g_id, String audioFile) {
		db = dbHelper.getWritableDatabase();

		String sql = "update " + AppDataBaseHelper.GROUPINFO + " set audiofile = '" + audioFile + "' where g_id = '"
				+ g_id + "'";
		db.execSQL(sql);
		db.close();
	}

	/**
	 * 通过g_id查询题组
	 * 
	 * @param g_id
	 * @return
	 */
	public synchronized GroupInfo queryById(String g_id) {
		db = dbHelper.getReadableDatabase();

		String sql = "select * from " + AppDataBaseHelper.GROUPINFO + " where g_id = '" + g_id + "' ";

		GroupInfo info = new GroupInfo();

		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			info.g_id = cursor.getString(1);
			info.category_id = cursor.getString(2);
			info.group_name = cursor.getString(3);
			info.group_cate = cursor.getString(4);
			info.group_type = cursor.getString(5);
			info.content = cursor.getString(6);
			info.img_1 = cursor.getString(7);
			info.img_2 = cursor.getString(8);
			info.audio = cursor.getString(9);
			info.audiofile = cursor.getString(10);
			info.is_right = cursor.getString(11);
			info.updateTime = cursor.getString(12);
			info.book_name = cursor.getString(13);
			info.task = cursor.getString(14);
			info.questions_list = questionDB.groupQuery(db, info.g_id);
		}

		cursor.close();
		db.close();

		return info;
	}

	/**
	 * 插入问题组
	 * 
	 */
	public synchronized void insert(GroupInfo groupInfo) {
		db = dbHelper.getWritableDatabase();

		// db.beginTransaction();
		String sql = "insert into "
				+ AppDataBaseHelper.GROUPINFO
				+ " (g_id,category_id,group_name,group_cate,group_type,content,img_1,img_2,audio,audiofile,is_right,updateTime,book_name,task) "
				+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		db.execSQL(sql, new Object[] { groupInfo.g_id, groupInfo.category_id, groupInfo.group_name,
				groupInfo.group_cate, groupInfo.group_type, groupInfo.content, groupInfo.img_1, groupInfo.img_2,
				groupInfo.audio, groupInfo.audiofile, groupInfo.is_right, groupInfo.updateTime, groupInfo.book_name,
				groupInfo.task });
		db.close();

	}

	/**
	 * 更新问题组
	 * 
	 */
	public synchronized void replaceInto(List<GroupInfo> groupInfos) {
		db = dbHelper.getWritableDatabase();
		try {
			db.beginTransaction();// 开始事务

			for (int i = 0; i < groupInfos.size(); i++) {
				String sql = "replace into "
						+ AppDataBaseHelper.GROUPINFO
						+ " (g_id,category_id,group_name,group_cate,group_type,content,img_1,img_2,audio,is_right,updateTime,book_name,task) "
						+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
				db.execSQL(sql,
						new Object[] { groupInfos.get(i).g_id, groupInfos.get(i).category_id,
								groupInfos.get(i).group_name, groupInfos.get(i).group_cate,
								groupInfos.get(i).group_type, groupInfos.get(i).content, groupInfos.get(i).img_1,
								groupInfos.get(i).img_2, groupInfos.get(i).audio, groupInfos.get(i).is_right,
								groupInfos.get(i).updateTime, groupInfos.get(i).book_name, groupInfos.get(i).task });

				questionDB.groupReplaceInto(groupInfos.get(i).questions_list, groupInfos.get(i).g_id, db);
			}
			db.setTransactionSuccessful();// 设置事务的标志为True
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.endTransaction();// 结束事务,有两种情况：commit,rollback,事务的提交或回滚是由事务的标志决定的,如果事务的标志为True，事务就会提交，否侧回滚,默认情况下事务的标志为False
			db.close();
		}
	}
}
